package com.css.bjdt_core.reportExplainFill.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.cell.service.CustomSqlService;
import com.css.bjdt_core.explain.entity.RptCellExplain;
import com.css.bjdt_core.explain.entity.RptTemplateExplain;
import com.css.bjdt_core.explain.entity.RptTemplateExplain.explainType;
import com.css.bjdt_core.explain.service.ReportTemplateExplainService;
import com.css.bjdt_core.explain.service.RptCellExplainService;
import com.css.bjdt_core.reportExplainFill.entity.ReportExplainFillEntity;
import com.css.bjdt_core.reportExplainFill.entity.ReportExplainTextFillEntity;
import com.css.bjdt_core.reportPrepare.entity.RptUnit;
import com.css.bjdt_core.reportPrepare.entity.RptUnitAnnualRel;
import com.css.bjdt_core.reportPrepare.service.RptUnitService;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.ReportConstant;

@Service
public class RptExplainCellFillService extends CustomSqlService {
	@Autowired
	private Dao dao;
	@Autowired
	private RptCellExplainService rptCellExplainService;

	@Autowired
	private ReportTemplateExplainService reportTemplateExplainService;

	@Autowired
	private RptExplainCellTextFillService rptExplainCellTextFillService;

	@Autowired
	private RptUnitService rptUnitService;

	//判断是否存在报表说明的存储表，如果不存在，创建该张批注表，如果存在，则什么都不做
	public void isExistTable(String annualId) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String count = executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		//Mysql没有表返回null oracle返回"0",兼容MYSQL和oracle
		if (count == null || count.equals("0")) {
			//if ("0".equals(count)) {
			executeSql(ReportConstant.EXPLAIN_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//判断是否存在报表说明中文本型的存储表，若不存在则创建，存在啥也不干
	public void isExistExplainTextTable(String annualId) {
		String tableName = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
		String count = executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		//Mysql没有表返回null oracle返回"0",兼容MYSQL和oracle
		if (count == null || count.equals("0")) {
			//if ("0".equals(count)) {
			executeSql(ReportConstant.EXPLAIN_TEXT_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//判断是否存在报表说明和报表说明文本的视图，若不存在则创建，存在就啥也不干
	public void isExistExplainTextView(String annualId) {
		String viewName = ReportConstant.EXPLAIN_TEXT_VIEW_PREFIX + annualId;
		String count = executeSql(ReportConstant.ISEXIST_VIEW_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", viewName)).getString();
		//Mysql没有表返回null oracle返回"0",兼容MYSQL和oracle
		if (count == null || count.equals("0")) {
			//if ("0".equals(count)) {
			String sqlStr = ReportConstant.CREATE_VIEW_EXPLAIN_TEXT_SQL;
			String tableExpl = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
			String tableText = ReportConstant.EXPLAIN_TEXT_VIEW_PREFIX + annualId;
			sqlStr.replace("$annualId", annualId).replace("$tableExpl", tableExpl).replace("$tableText", tableText);
			Sql sql = Sqls.create(sqlStr);
			dao.execute(sql);
		}
	}

	//插入一条批注信息
	public void insertAExplainCellValue(String annualId, ReportExplainFillEntity reportExplainFillEntity) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_INSERT_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("EXPLAIN_CELL_DATA_ID", CommonUtil.getUUID());
		sql.params().set("EXPLAIN_CELL_VALUE", reportExplainFillEntity.getExplainCellValue());
		sql.params().set("ABS_ROW_NUM", reportExplainFillEntity.getAbsRowNum());
		sql.params().set("ABS_COL_NUM", reportExplainFillEntity.getAbsColNum());
		sql.params().set("DZZ_ID", reportExplainFillEntity.getDzzId());
		sql.params().set("EXPLAIN_ID", reportExplainFillEntity.getExplainId());
		sql.params().set("IS_SAME_ROW", reportExplainFillEntity.getIsSameRow());
		sql.params().set("SUM_STATUS", reportExplainFillEntity.getSumStatus());
		dao.execute(sql);
	}

	//插入一条批注文本信息
	public void insertAExplainCellTextValue(String annualId, ReportExplainTextFillEntity reportExplainTextFillEntity) {
		String tableName = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_TEXT_INSERT_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("EXPLAIN_TEXT_ID", reportExplainTextFillEntity.getExplainCellValueId());
		sql.params().set("EXPLAIN_TEXT", reportExplainTextFillEntity.getExplainCellText());
		sql.params().set("EXPLAIN_ID", reportExplainTextFillEntity.getExplainId());
		sql.params().set("DZZ_ID", reportExplainTextFillEntity.getDzzId());
		dao.execute(sql);
	}

	public void insertExplainCellValueList(String annualId, List<ReportExplainFillEntity> reportExplainList) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;

		if (reportExplainList != null && reportExplainList.size() > 0) {
			List<List<ReportExplainFillEntity>> lists = CommonUtil.subList(reportExplainList, 40);
			List<Sql> sqls = new ArrayList<Sql>();
			for (List<ReportExplainFillEntity> list : lists) {
				StringBuilder batchInsertReportExplainSql = new StringBuilder();
				batchInsertReportExplainSql.append("INSERT ALL ");

				for (ReportExplainFillEntity explain : list) {
					batchInsertReportExplainSql.append("INTO ").append(tableName).append(" VALUES (")
							.append(addQuotesStr(CommonUtil.getUUID())).append(",")
							.append(addQuotesStr(explain.getExplainCellValue())).append(",")
							.append(addQuotesStr(explain.getAbsRowNum())).append(",")
							.append(addQuotesStr(explain.getAbsColNum())).append(",")
							.append(addQuotesStr(explain.getDzzId())).append(",")
							.append(addQuotesStr(explain.getExplainId())).append(",")
							.append(addQuotesStr(explain.getIsSameRow())).append(",")
							.append(addQuotesStr(explain.getSumStatus())).append(")  ");
				}
				batchInsertReportExplainSql.append(" SELECT * FROM dual");
				try {
					Sql batchInsertsql = Sqls.create(batchInsertReportExplainSql.toString());
					sqls.add(batchInsertsql);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			dao.execute(sqls.toArray(new Sql[0]));
		}
	}

	public void insertExplainCellTextValueList(String annualId, List<ReportExplainTextFillEntity> explainTextList) {
		String tableName = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
		if (explainTextList != null && explainTextList.size() > 0) {
			List<List<ReportExplainTextFillEntity>> lists = CommonUtil.subList(explainTextList, 40);
			List<Sql> sqls = new ArrayList<Sql>();
			for (List<ReportExplainTextFillEntity> list : lists) {
				StringBuilder batchInsertTextSql = new StringBuilder();
				batchInsertTextSql.append("INSERT ALL ");

				for (ReportExplainTextFillEntity text : list) {
					batchInsertTextSql.append("INTO ").append(tableName).append(" VALUES (")
							.append(addQuotesStr(text.getExplainCellValueId())).append(",")
							.append(addQuotesStr(text.getExplainCellText())).append(",")
							.append(addQuotesStr(text.getExplainId())).append(",").append(addQuotesStr(text.getDzzId()))
							.append(")  ");
				}
				batchInsertTextSql.append(" SELECT * FROM dual");
				try {
					Sql batchInsertsql = Sqls.create(batchInsertTextSql.toString());
					sqls.add(batchInsertsql);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			dao.execute(sqls.toArray(new Sql[0]));
		}
	}

	//加个单引号给传进来的字符串（专门为数据汇总批量插入方法）
	public String addQuotesStr(String str) {
		return str == null ? null : "'" + str + "'";
	}

	//根据报表说明实例ID和年度ID
	public List<ReportExplainFillEntity> getAllExamineInfoByTemplateDataId(String explainId, String annualId,
			String dzzId) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String strSql = "";
		//0是未汇总，1是已汇总
		RptUnitAnnualRel rptUnitAnnualRel = rptUnitService.getARptUnitAnnualRelByDzzIdAnnualId(dzzId, annualId);
		RptUnit rptUnit = rptUnitService.queryById(rptUnitAnnualRel.getUnitId());
		if (rptUnit.getUnitType().equals("1")) {
			strSql = ReportConstant.EXPLAIN_QUERY_SQL.replace("$table_name", tableName);
		} else {
			strSql = ReportConstant.EXPLAIN_QUERY_SUMMARY_SQL.replace("$table_name", tableName);
		}
		Sql sql = Sqls.create(strSql);
		sql.params().set("EXPLAIN_ID", explainId).set("DZZ_ID", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<ReportExplainFillEntity> reportExplainFillEntityList = new ArrayList<ReportExplainFillEntity>();
				while (rs.next()) {
					ReportExplainFillEntity reportExplainFillEntity = new ReportExplainFillEntity();
					reportExplainFillEntity.setExplainId(rs.getString("EXPLAIN_ID"));
					reportExplainFillEntity.setAbsColNum(rs.getString("ABS_COL_NUM"));
					reportExplainFillEntity.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					reportExplainFillEntity.setDzzId(rs.getString("DZZ_ID"));
					reportExplainFillEntity.setExplainCellValue(rs.getString("EXPLAIN_CELL_VALUE"));
					reportExplainFillEntity.setExplainCellDataId(rs.getString("EXPLAIN_CELL_DATA_ID"));
					reportExplainFillEntity.setIsSameRow(rs.getString("IS_SAME_ROW"));
					reportExplainFillEntity.setSumStatus(rs.getString("SUM_STATUS"));
					reportExplainFillEntityList.add(reportExplainFillEntity);
				}
				return reportExplainFillEntityList;
			}
		});
		dao.execute(sql);
		return sql.getList(ReportExplainFillEntity.class);
	}

	public Map<String, List<ReportExplainFillEntity>> isHaveExplainCellValue(String explainId, String annualId,
			String dzzId) {
		Map<String, List<ReportExplainFillEntity>> map = new HashMap<String, List<ReportExplainFillEntity>>();
		List<RptCellExplain> rptCellExplainList = rptCellExplainService.queryAllCellExplainByExplainId(explainId);
		List<ReportExplainFillEntity> reportExplainFillEntityList = getAllExamineInfoByTemplateDataId(explainId,
				annualId, dzzId);
		List<ReportExplainFillEntity> list = new ArrayList<ReportExplainFillEntity>();
		RptTemplateExplain rptTemplateExplain = reportTemplateExplainService.getARptTemplateExplain(explainId);
		if (rptTemplateExplain.getExplainType().equals("3")) {
			//Map<String, ReportExplainFillEntity> reportExplainFillEntityMap = new HashMap<String, ReportExplainFillEntity>();
			for (RptCellExplain rptCellExplain : rptCellExplainList) {
				ReportExplainFillEntity reportExplainFillEntity = new ReportExplainFillEntity();
				reportExplainFillEntity.setAbsColNum(rptCellExplain.getAbsColNum() + "");
				reportExplainFillEntity.setAbsRowNum(rptCellExplain.getAbsRowNum() + "");
				reportExplainFillEntity.setValueLimited(3000);
				list.add(reportExplainFillEntity);
			}
			for (ReportExplainFillEntity reportExplainFillEntity : reportExplainFillEntityList) {
				String str = rptExplainCellTextFillService.getExplainCellTextValue(annualId,
						reportExplainFillEntity.getExplainCellValue());
				reportExplainFillEntity.setExplainCellValue(str);
				reportExplainFillEntity.setValueLimited(3000);
			}
		} else {
			for (RptCellExplain rptCellExplain : rptCellExplainList) {
				ReportExplainFillEntity reportExplainFillEntity = new ReportExplainFillEntity();
				reportExplainFillEntity.setAbsColNum(rptCellExplain.getAbsColNum() + "");
				reportExplainFillEntity.setAbsRowNum(rptCellExplain.getAbsRowNum() + "");
				list.add(reportExplainFillEntity);
			}
		}
		map.put("top", list);
		map.put("body", reportExplainFillEntityList);
		return map;

	}

	public void deleteAllCellExplainByExplainId(String explainId, String annualId, String dzzId) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_DELETE_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("EXPLAIN_ID", explainId).set("DZZ_ID", dzzId);
		dao.execute(sql);
	}

	//删除这张文本报表说明的真实数据
	public void deleteAllCellTextExplainByExplainId(String explainId, String annualId, String dzzId) {
		String tableName = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_TEXT_DELETE_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("explaInId", explainId).set("dzzId", dzzId);
		dao.execute(sql);
	}

	//根据党组织ID删除
	public void deleteAllCellExplainByDzzId(String annualId, String dzzId) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_DELETE_SQL_DZZID.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("DZZ_ID", dzzId);
		dao.execute(sql);
	}

	//根据党组织ID删除
	public void deleteAllCellExplainByTemplate(String annualId, String dzzId, List<String> templateIds) {
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		String strSql = "delete from $tableName a where a.DZZ_ID = @dzzId "
				+ " and EXISTS (select 1 from RPT_TEMPLATE_EXPLAIN b where a.EXPLAIN_ID = b.ID and b.TEMPLATE_ID in ( $templateIds )) ";
		Sql sql = Sqls.create(strSql);
		sql.params().set("DZZ_ID", dzzId);
		sql.vars().set("templateIds", CommonUtil.convertIDList(templateIds)).set("tableName", tableName);
		dao.execute(sql);
	}

	//根据党组织ID删除这张文本报表说明的真实数据
	public void deleteAllCellTextExplainByDzzId(String annualId, String dzzId) {
		String tableName = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXPLAIN_TEXT_DELETE_SQL_DZZID.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		dao.execute(sql);
	}

	//查询某报表模板、党组织、报表说明所对应的不为空的报表说明值的个数
	public int countExplainValue(String annualId, String dzzId, String explainId) {
		//先根据报表说明ID查询出来这个报表说明属于哪一类
		RptTemplateExplain rptTemplateExplain = reportTemplateExplainService.getARptTemplateExplain(explainId);
		String tableName = ReportConstant.EXPLAIN_TABLE_PREFIX + annualId;
		//条目型和数字型的判断是否为空表
		String strSql = "";
		if (rptTemplateExplain.getExplainType().equals(explainType.entry)
				|| rptTemplateExplain.getExplainType().equals(explainType.number.getKey())) {
			strSql = "select count(1) from (select replace(t3.explain_cell_value,' ','')  trimExplainCellValue,t3.* from "
					+ " $tableName t3 where t3.EXPLAIN_ID = @explainId and t3.DZZ_ID = @dzzId ) where trimExplainCellValue is not null";
			Sql sql = Sqls.create(strSql);
			sql.params().set("explainId", explainId).set("dzzId", dzzId);
			sql.vars().set("tableName", tableName);
			sql.setCallback(Sqls.callback.integer());
			dao.execute(sql);
			return sql.getObject(Integer.class);
		} else if (rptTemplateExplain.getExplainType().equals(explainType.textStr.getKey())) {
			String tableNameText = ReportConstant.EXPLAIN_TEXT_TABLE_PREFIX + annualId;
			strSql = "select count(1) from (select t1.explain_cell_value, replace(t2.explain_text,' ','') explainText "
					+ " from $tableName t1 left join $tableNameText t2 on t1.explain_cell_value = t2.explain_text_id "
					+ " where t1.EXPLAIN_ID = @explainId and t1.DZZ_ID = @dzzId) "
					+ " where explainText is not null and DBMS_LOB.GETLENGTH(explainText) <> 0";
			Sql sql = Sqls.create(strSql);
			sql.params().set("explainId", explainId).set("dzzId", dzzId);
			sql.vars().set("tableName", tableName).set("tableNameText", tableNameText);
			sql.setCallback(Sqls.callback.integer());
			dao.execute(sql);
			return sql.getObject(Integer.class);
		} else {
			return 0;
		}

		/*return dao.count(tableName, Cnd.where("DZZ_ID", "=", dzzId).and("EXPLAIN_ID", "=", explainId)
				.and("EXPLAIN_CELL_VALUE", "is not", null));*/
	}
}
